package com.bwlcums.preview.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Service;

import com.bwlcums.utils.StringUtils;
import com.bwlcums.utils.SystemContext;

@Service
public class FileListPreviewServiceImpl implements FileListPreviewService {
	
	@Override
	public void saveFileInfo(final String oname, final String filename, final String fullpath,
			final String ip, final  long size, final byte[] datas) {
		String sql = "insert into filedesc (id, oname, filename, fullpath, ip, size, setdate, fdata) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?)";
		LobHandler lobHandler = new DefaultLobHandler();  
		try {
			getJdbcTemplate().execute(sql,
					       new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
					        protected void setValues(PreparedStatement pstmt, LobCreator lobCreator) throws SQLException {
					        	String id = UUID.randomUUID().toString().replaceAll("-", "");
					        	pstmt.setString(1, id);
								pstmt.setString(2, oname);
								pstmt.setString(3, filename);
								pstmt.setString(4, fullpath);
								pstmt.setString(5, ip);
								pstmt.setLong(6, size);
								pstmt.setLong(7, System.currentTimeMillis());
								pstmt.setBytes(8, datas);
					        }
					      }
					  );
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public List<Map<String, Object>> getFilesInfoDataList() {
		String querysql = "select id, oname, filename, fullpath, ip, size, setdate  from filedesc order by setdate desc";
		List<Map<String, Object>> list = null;
		 try {
			list =  getJdbcTemplate().queryForList(querysql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		 
		 if(list!=null){
			 for(Map<String, Object> map:list){
				 if(map!=null){
					 long size = Long.parseLong(""+map.get("size"));
					 map.put("size", StringUtils.convertFileSize(size));
					 long setdate = Long.parseLong(""+map.get("setdate"));
					 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					 map.put("setdate", sdf.format(setdate));
				 }
			 }
		 }

		return list;
	}

	@Override
	public boolean deleteFile(String id) {
		String sql = "delete from filedesc where id=?";
		boolean flag = false;
		try {
			int i = getJdbcTemplate().update(sql, id);
			if(i>0){
				flag = true;
			}
		} catch (DataAccessException e) {
			e.printStackTrace();
		}

		return flag;
	}

	@Override
	public long getAllFileLength() {
		String sql = "select sum(size) from filedesc";
		long length = 0;
		try {
			length = getJdbcTemplate().queryForObject(sql, Long.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return length;
	}

	@Override
	public long getAllFileNum() {
		String sql = "select count(*) from filedesc";
		long count = 0;
		try {
			count = getJdbcTemplate().queryForObject(sql, Long.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	private JdbcTemplate getJdbcTemplate(){
		
		return (JdbcTemplate) SystemContext.getBean("sqliteJdbcTemplate");
	}

	@Override
	public byte[] getImageData(String fname) {
		if(!StringUtils.isNotNullOrEmpty(fname)){
			return null;
		}
		String sql = "select filename, fdata from filedesc where filename=?";
		
		Object[] args = new Object[]{fname};
		Long count = getJdbcTemplate().queryForObject("select count(*) from ("+sql+")", Long.class, args);
		if(count==null || count<1){
			return null;
		}

		ResultSetExtractor<byte[]> rse = new ResultSetExtractor<byte[]>() {
			@Override
			public byte[] extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				return rs.getBytes("fdata");
			}
		};
		byte[] bytes = getJdbcTemplate().query(sql, rse, args);
		
		return bytes;
	}

}
